package com.szholly.pro.watergas_server.ps.controller;

import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.web.BaseController;
import com.szholly.pro.watergas_server.common.web.RequestUnit;
import com.szholly.pro.watergas_server.ps.service.OperationDataVaildService;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

import net.sf.json.JSONObject;

/**
 * 有效性统计
 * @author wangl
 *
 * 2016年6月17日
 */
@Controller
@RequestMapping("/IndusteryPollutQuarterDataValidation/*")
public class IndustryPollQuarterDataValidationController extends BaseController {
	@Autowired
	OperationDataVaildService  operationDataVaildService;
	protected Logger logger = LoggerFactory.getLogger(getClass());
	/**
	 * 单个任务统计通用入口
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/getOneTaskStatistics")
	public void getOneTaskStatistics(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String, String> params = getParams();
		RowBase data=new RowBase();
		data=getTaskStatistics(params.get("START_TIME"),params.get("END_TIME"),params.get("STATION_ID"),params.get("JOB_ID"),params.get("TYPE")); 
		List<RowBase> list=new ArrayList<RowBase>();
		list.add(data);
		JSONObject json = DataUtil.listToJSONObject(list, null, null);
		outString(json.toString());
	}
	/**
	 * 获取所有任务统计
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/getAllTaskStatistics")
	public void getAllTaskStatistics(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String, String> params = getParams();
		RowBase data=new RowBase();
		List<RowBase> list=new ArrayList<RowBase>();
		String jobId="";
		for(int i=1;i<9;i++){
			if(i==1){
				jobId="fa3ecf1a-4820-4f9f-b7b7-2553584ee7ba";
			}
			if(i==2){
				jobId="0bddcd82-73ff-4ee7-85af-491108b7f374";
			}
			if(i==3){
				jobId="2bf6c8b1-12c3-496c-8e1b-96b52157e12c";
			}
			if(i==4){
				jobId="5ed321b7-99ca-4f0b-b81f-008475d0174a";
			}
			if(i==5){
				jobId="167b2c94-4c13-45fd-b796-8c0e4bf3f6b9";
			}
			if(i==6){
				jobId="0f9faa25-89e4-4ad6-869f-aa1beb70f7bc";
			}
			if(i==7){
				jobId="21c74765-685e-4a89-a625-0fa8f3668fc5";
			}
			if(i==8){
				jobId="5385ab20-26ee-4fd0-a419-1cd9174a9921";
			}
			data=getTaskStatistics(params.get("START_TIME"),params.get("END_TIME"),params.get("STATION_ID"),jobId,i+""); 
			list.add(data);
		}
		JSONObject json = DataUtil.listToJSONObject(list, null, null);
		outString(json.toString());
	}
	/**
	 * 获取某个运维任务统计
	 * @param st 开始时间
	 * @param et 结束时间
	 * @param stationId 站点id
	 * @param jobId 事件id
	 * @param type 任务类型,1为日常巡检,2为定期维护，3为超标比对，4为设备故障维修，5为备件及耗材更换，6为水样比对，7为标样核查，8为日常校准
	 * @throws Exception
	 */
	public RowBase getTaskStatistics(String st,String et,String stationId,String jobId,String type) throws Exception {
		RowBase data=new RowBase();
	      //获取任务周期
		RowBase r=getJobCycle(stationId,jobId);
		//周期
		String cycle="";
		//事件名称
		String jobNmae="";
		//时间要求
		String cyclename="";
		//事件id
		String job_id="";
		if(r.size()>0){
			cycle=r.getSafeString("TASK_CYCLE");
			jobNmae=r.getSafeString("JOB_NAME");
			cyclename=r.getSafeString("CYCLENAME");
			job_id=r.getSafeString("JOB_ID");
			data.put("JOB_NAME", jobNmae);
			data.put("TIME_REQUIRE", cyclename);
		}
		else{
			data= null;
			return data;
		}
	      //获取应完成任务总数
	     String count=getTaskCount(cycle,st,et);
	     //实际完成任务数
	     int trueCount=getTaskTrueCount( type, st, et, stationId);
	     String trueCountStr=trueCount+"";
	     if(jobId.equals("2bf6c8b1-12c3-496c-8e1b-96b52157e12c")||jobId.equals("5ed321b7-99ca-4f0b-b81f-008475d0174a")||jobId.equals("167b2c94-4c13-45fd-b796-8c0e4bf3f6b9")){
	    	 count=trueCountStr;
	     }
	     if(trueCount>Integer.parseInt(count)){
	    	 trueCountStr=count;
	     }
	     float trueCountRate=Float.parseFloat(trueCountStr)/Float.parseFloat(count);
	     //获取按时完成率
	     String CompletionRate= getCompletionRate(type, st, et,stationId,trueCountRate);
	     //获取结果通过率
	    String PassingRate =getResultRate( type, st, et,stationId);
	     //获取有效性判断结果
	    String result= getJudgeData( type, count, PassingRate, CompletionRate, st, et,stationId);
	    data.put("TASK_TOTAL", count);
	    data.put("TASK_INTIMEPERCENT", CompletionRate);
	    data.put("PASSED_PERCENT", PassingRate);
	    data.put("VALIDATION_RESULT", result);
	    data.put("JOB_ID", job_id);
	    data.put("TASK_REAL", trueCountStr);
	    return data;
	}
	/**
	 * 获取参数
	 * @return
	 */
	private Map<String,String> getParams(){
		String st=RequestUnit.getParameter("START_TIME");
	      String et=RequestUnit.getParameter("END_TIME");
	      String stationId=RequestUnit.getParameter("STATION_ID");
	      String jobId=RequestUnit.getParameter("JOB_ID");
	      String type=RequestUnit.getParameter("TYPE");
	      String wwtp=RequestUnit.getParameter("WWTP_ID");
	      String flowname=RequestUnit.getParameter("FLOW_NAME");
	      String hour=RequestUnit.getParameter("HOUR");//几小时上传一条数据
	      String errorStartTime=RequestUnit.getParameter("ERROR_START_TIME");//故障开始时间
	      String errorEndTime=RequestUnit.getParameter("ERROR_END_TIME");//故障结束时间
	      
	      String manualFlag=RequestUnit.getParameter("AUTO_OR_MANUAL");//几小时上传一条数据
	      String DATA_TYPE=RequestUnit.getParameter("DATA_TYPE");//几小时上传一条数据
	      String GUID=RequestUnit.getParameter("GUID");//几小时上传一条数据
	      List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
	      
		Map<String, String> params = new HashMap<String, String>();
		if(StringUtil.IsNotNullOrEmpty(st)){
			params.put("START_TIME", st);
		}
		if(StringUtil.IsNotNullOrEmpty(et)){
			params.put("END_TIME", et);
		}
		if(StringUtil.IsNotNullOrEmpty(stationId)){
			params.put("STATION_ID", stationId);
		}
		if(StringUtil.IsNotNullOrEmpty(jobId)){
			params.put("JOB_ID", jobId);
		}
		if(StringUtil.IsNotNullOrEmpty(type)){
			params.put("TYPE", type);
		}
		if(StringUtil.IsNotNullOrEmpty(hour)){
			hour=RequestUnit.getParameter("HOUR");
		}
		else{
			hour="1";
		}
		params.put("HOUR", hour);
		if(StringUtil.IsNotNullOrEmpty(errorStartTime)){
			params.put("ERROR_START_TIME", errorStartTime);
		}
		if(StringUtil.IsNotNullOrEmpty(errorEndTime)){
			params.put("ERROR_END_TIME", errorEndTime);
		}
		if(StringUtil.IsNotNullOrEmpty(manualFlag)){
			params.put("MANUAL_FLAG", manualFlag);
		}
		if(StringUtil.IsNotNullOrEmpty(DATA_TYPE)){
			params.put("DATA_TYPE", DATA_TYPE);
		}
		return params;
	}
	/**
	 * 获取任务周期
	 * @param stationId 站点id
	 * @param jobId 事件id
	 * @return
	 */
	public RowBase getJobCycle(String stationId,String jobId){
		RowBase result=null;
		String sql=" select t1.TASK_CYCLE,t2.job_name,d.s_domaincaption as cyclename,t2.guid as job_id  from OC_TASK_CONFIG t1 left join operation_job t2 on t1.job_id=t2.guid "
				+ "left join (select t.S_DOMAINCODE,t.S_DOMAINCAPTION  from data_defulatdomain  t where S_DOMAINNAME='OPERATION_CYCLE_TIME') d on t1.task_cycle=d.s_domaincode where 1=1 ";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		if (jobId != null && !jobId.equals("")) {
					sql += " and t1.JOB_ID =@jobId";
					parms.add(new GlobalParameter("jobId", jobId));
		 }
		if (stationId != null && !stationId.equals("")) {
			sql += " and t1.STATION_ID =@stationId";
			parms.add(new GlobalParameter("stationId", stationId));
         }
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if(rows.size()>0){
			result=rows.get(0);
		}
		return result;
	}
	/**
	 * 获取应完成任务总数
	 * @param cycle 任务周期
	 * @param st  开始时间
	 * @param et  结束时间
	 * @return
	 */
	public String getTaskCount(String cycle,String st,String et){
		//一小时有多少毫秒
		 long nh = 1000 * 60 * 60;
		 //周期有多少个小时
		 long hour=0;
		 //实际应该上传任务数
		 String result="";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date ct_date=new Date ();
		Date et_date=new Date();
		try {
			ct_date = sdf.parse(st);
			et_date = sdf.parse(et);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// 获得两个时间的毫秒时间差异
	    long diff = et_date.getTime() - ct_date.getTime();
	    // 计算差多少小时
	    long counthour = diff / nh;
	    int timeCycleNum = Integer.parseInt(cycle.replaceAll("[a-zA-Z]", ""));
	    //天
	    if(cycle.indexOf("D")>0){
	    	hour=timeCycleNum*24;
	    }
	    //周
	    if(cycle.indexOf("W")>0){
	    	hour=timeCycleNum*24*7;
	    }
	    //月
	    if(cycle.indexOf("M")>0){
	    	hour=timeCycleNum*24*30;
	    }
	   //小时
	    if(cycle.indexOf("H")>0){
	    	hour=timeCycleNum;
	    }
	     result= counthour/hour+"";
	    return result;
	}
	/**
	 * 获取实际完成任务总数
	 * @param type 事件类型
	 * @param st 开始时间
	 * @param et  结束时间
	 * @param staionId 站点id
	 * @return
	 */
	public int getTaskTrueCount(String type,String st,String et,String staionId){
		//获取任务总数
		String sql=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code where t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+staionId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> allrow = DataUtil.getSelectRows(sql, parms);
		int a=allrow.size();
		return a;
	}
	/**
	 * 获取按时完成率
	 * @param type 事件类型
	 * @param st 开始时间
	 * @param et 结束时间
	 * @param staionId 站点Id
	 * @param trueCountRate 实际完成任务率
	 * @return
	 */
	public String getCompletionRate(String type,String st,String et,String staionId,float trueCountRate){
		//结果
		String result="";
		//获取任务总数
		String sql=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code where t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+staionId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> allrow = DataUtil.getSelectRows(sql, parms);
		int a=allrow.size();
		//获取按时完成总数
		String sqlr=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code where t1.IS_TIMEOUT='1' and t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+staionId+"'";
		List<RowBase> validrow = DataUtil.getSelectRows(sqlr, parms);
		float b=(float)validrow.size();
		
		if(a>0&&b>0){
			if(b>a){
				b=a;
			}
			float f=(b/a)*trueCountRate;
			DecimalFormat df = new DecimalFormat("#.00");//格式化小数，不足的补0
			float r=f*100;
			String filesize = df.format(r);
			if(r==100.0){
				filesize="100";
			}
			result=filesize+"%";
		}
		else{
			result="0%";
		}
		return result;
	}
	/**
	 * 获取结果通过率
	 * @param type 事件类型
	 * @return
	 */
	public String getResultRate(String type,String st,String et,String staionId){
		//结果
		String result="";
		//获取任务总数
		String sql=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code where t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
		          +" and t2.station_id='"+staionId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> allrow = DataUtil.getSelectRows(sql, parms);
		int a=allrow.size();
		//获取有效数据
		String sqlr=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code where IS_VALID='1' and t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+staionId+"'";
		List<RowBase> validrow = DataUtil.getSelectRows(sqlr, parms);
		float b=(float)validrow.size();
		if(a>0&&b>0){
			if(b>a){
				b=a;
			}
		float f=b/a;
		DecimalFormat df = new DecimalFormat("#.00");//格式化小数，不足的补0
		float r=f*100;
		String filesize = df.format(r);
        if(r==100.0){
        	filesize="100";
        }
		result=filesize+"%";
		}
		else{
			result="0%";
		}
		return result;
	}
	/**
	 * 获取有效性判断结果
	 * @param type 事件类型
	 * @param count 应完成任务数
	 * @param PassingRate 通过率
	 * @param CompletionRate 按时完成率
	 * @param st 开始时间
	 * @param et 结束时间
	 * @return
	 */
	public String getJudgeData(String type,String count,String PassingRate,String CompletionRate,String st,String et,String staionId){
		String result="";
		String sql=" select t1.* from OP_EFFE t1 left join OP_TASK t2 on t1.task_code=t2.task_code  where t1.TASK_TYPE='"+type+"'"+" and t1.ct >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.ct <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+staionId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> allrow = DataUtil.getSelectRows(sql, parms);
		if(Integer.parseInt(count)<=allrow.size()){
			float Nump = Float.parseFloat(PassingRate.replaceAll("%", ""));
			float Numc = Float.parseFloat(CompletionRate.replaceAll("%", ""));
			if(Nump>=75&&Numc>=75){
				result="1";
			}
			else{
				result="0";
			}
		}
		else{
			result="0";
		}
		return result;
	}
  /**
   * 获取设备运转率
   * @param resq
   * @param res
   * @throws Exception
   */
	@RequestMapping(value="/getOperationRate")
	public void getOperationRate(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String, String> params = getParams();
		DecimalFormat decimalFormat=new DecimalFormat("#.00##");
		List<RowBase> result=new ArrayList<RowBase>();
		RowBase r;
		//第一步获取站点运行时间
		String alltime=getStationTime(params.get("START_TIME"),params.get("END_TIME"));
		float alltimeFloat=Float.parseFloat(alltime);
		//第二步获取站点设备
		List<RowBase> list=getOperation(params.get("STATION_ID"));
		String one="";
		String count="";
		if(list.size()>0){
			for(int i=0;i<list.size();i++){
				r=new RowBase();
				String parm=list.get(i).getSafeString("MONITOR_POLLUTANTS");
				r.put("DEVICE_NAME", list.get(i).getSafeString("NAME"));//设备名称
				r.put("DEVICE_NUM", list.get(i).getSafeString("DEVICE_NUM"));//设备编号
				one=getOneDroppedTime(parm,params.get("START_TIME"),params.get("END_TIME"),params.get("STATION_ID"));
				count=getDropCount(params.get("STATION_ID"),params.get("START_TIME"),params.get("END_TIME"),parm);
				float oneFloat=Float.parseFloat(one);
				r.put("DEVICE_FAILURE_TIME", one);//累积故障时间
				float f=(alltimeFloat-oneFloat)/alltimeFloat;
				String rate=decimalFormat.format(f);
				r.put("DEVICE_OPRERATE_RATE", rate);//设备运转率
				r.put("DEVICE_MTBS", alltimeFloat-oneFloat);//平均无故障时间
				r.put("STATION_OPERATE_TIME", alltime);//站点运行时间
				r.put("DEVICE_FAILURE_NUM", count);//故障次数
				result.add(r);
			}
		}
		JSONObject json = DataUtil.listToJSONObject(result, null, null);
		outString(json.toString());
		
	}
	/**
	 * 获取站点运行时间，默认站点24小时都在运行
	 * @param st 开始时间
	 * @param et 结束时间
	 * @return
	 */
	private String getStationTime(String st,String et){
		//一小时有多少毫秒
		 long nh = 1000 * 60 * 60;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date ct_date=new Date ();
		Date et_date=new Date();
		try {
			ct_date = sdf.parse(st);
			et_date = sdf.parse(et);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		// 获得两个时间的毫秒时间差异
	    long diff = et_date.getTime() - ct_date.getTime();
	    // 计算差多少小时
	    long counthour = diff / nh;
	    //如果是23:59:59就加1
	    if(et.contains("23:59:59")){
	    	counthour=counthour+1;
	    }
	    return counthour+"";
	}
	/**
	 * 根据站点Id获取设备
	 * @param stationId 站点Id
	 * @return
	 */
	private List<RowBase> getOperation(String stationId){
		String sql="select * from BC_SITE_DEVICE_RPT where MONITOR_POINT='"+stationId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row;
	}
	/**
	 * 获取单个设备故障时间
	 * @param param 设备参数
	 * @param st 开始时间
	 * @param et 结束时间
	 * @param stationId  站点id
	 * @return
	 */
	private String getOneDroppedTime(String param,String st,String et,String stationId){
		String result="";
		/*String sql="select sum(CONTINUE_TIME) as time from OP_ALAR_INFO where type='3' and PARAM='"+param+"' and STATION_CODE='"+stationId+"'"
				+" and CREATETIME >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and CREATETIME <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";*/
		String sql="select sum(t.time) as time from (select ROUND(TO_NUMBER(t1.end_time - t1.begin_time) * 24 * 60) as time from operation_failure_repair t1 left join BC_SITE_DEVICE_RPT t2 on t1.device_number=t2.device_num where t1.end_time  is not null and t1.begin_time is not null "
				+" and t1.end_time >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+"  and t1.end_time <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"+" and t2.monitor_pollutants='"+param+"' and t1.STATION_ID='"+stationId+"')t";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			if(row.get(0).getSafeString("TIME")!=null){
				String time=row.get(0).getSafeString("TIME").toString();
				DecimalFormat decimalFormat=new DecimalFormat("#.00##");
				float f=0;
				if (time != null && !time.equals("")) {
					float se=Float.parseFloat(time);
					 f=se/60;
				}
				else{
					f=0;
				}
				if(f==0){
					result="0";
				}
				else{
					result=decimalFormat.format(f);
				}
				
			}
			else{
				result="0";
			}
		}
		else{
			result="0";
		}
		return result;
	}
	/**
	 * 获取设备故障次数
	 * @param stationId 站点Id
	 * @param st 开始时间
	 * @param et 结束时间
	 * @return
	 */
	private String getDropCount(String stationId,String st,String et,String param){
		String result="";
		/*String sql="select count(STATION_CODE) as count from OP_ALAR_INFO where type='3' and STATION_CODE='"+stationId+"'"+" and CREATETIME >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and CREATETIME <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and PARAM='"+param+"'";*/
		String sql="select count(t1.STATION_ID) as count from operation_failure_repair t1 left join BC_SITE_DEVICE_RPT t2 on t1.device_number=t2.device_num where t1.end_time  is not null and t1.begin_time is not null "
				+" and t1.end_time >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+"  and t1.end_time <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"+" and t2.monitor_pollutants='"+param+"' and t1.STATION_ID='"+stationId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			result=row.get(0).getSafeString("COUNT").toString();
		}
		else{
			result="0";
		}
		return result;
	}
	/**
	 * 获取数采仪在线率
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/getDataLoggerRate")
	public void getDataLoggerRate(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String, String> params = getParams();
		DecimalFormat decimalFormat=new DecimalFormat("#.00##");
		//第一步获取站点运行时间
		String alltime=getStationTime(params.get("START_TIME"),params.get("END_TIME"));
		float alltimeF=Float.parseFloat(alltime);
		//第二步获取数采仪掉线时间
		String droptime=getDataLoggerDropTime(params.get("STATION_ID"),params.get("START_TIME"),params.get("END_TIME"));
		float droptimeF=Float.parseFloat(droptime);
		//获取数采仪掉线次数
		String count=getscyDropCount(params.get("STATION_ID"),params.get("START_TIME"),params.get("END_TIME"));
		List<RowBase> result=new ArrayList<RowBase>();
		RowBase r=new RowBase();
		r.put("STATION_OPRERATE_TIME", alltime);//站点运行时间
		r.put("OFFLINE_NUM", count);//掉线次数
		r.put("OFFLINE_TIME", droptime);//累积掉线时间
		r.put("ONLINE_TIME", alltimeF-droptimeF);//累积在线时间
		float f=(alltimeF-droptimeF)/alltimeF;
		String rate=decimalFormat.format(f);
		r.put("ONLINE_RATE", rate);//数采仪在线率
		r.put("MN", getDataLoggerNum(params.get("STATION_ID")));//数采仪编号
		result.add(r);
		JSONObject json = DataUtil.listToJSONObject(result, null, null);
		outString(json.toString());
	}
	/**
	 * 获取数采仪掉线时间
	 * @param stationId 站点id
	 * @return
	 */
	private String getDataLoggerDropTime(String stationId,String st,String et){
		String result="";
		String sql="select sum(CONTINUE_TIME) as time from OP_ALAR_INFO where type='4' and STATION_CODE='"+stationId+"'"+" and CREATETIME >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and CREATETIME <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			String time=row.get(0).getSafeString("TIME").toString();
			DecimalFormat decimalFormat=new  DecimalFormat("#.00##");
			float f=0;
			if (time != null && !time.equals("")) {
				float se=Float.parseFloat(time);
				 f=se/60;
			}
			else{
				f=0;
			}
			result=decimalFormat.format(f);
		}
		else{
			result="0";
		}
		return result;
	}
	/**
	 * 获取数采仪掉线次数
	 * @param stationId
	 * @param st
	 * @param et
	 * @param param
	 * @return
	 */
	private String getscyDropCount(String stationId,String st,String et){
		String result="";
		String sql="select count(STATION_CODE) as count from OP_ALAR_INFO where type='4' and STATION_CODE='"+stationId+"'"+" and CREATETIME >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and CREATETIME <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			result=row.get(0).getSafeString("COUNT").toString();
		}
		else{
			result="0";
		}
		return result;
	}
	/**
	 * 获取数采仪编号
	 * @param stationId
	 * @return
	 */
	private String getDataLoggerNum(String stationId){
		String result="";
		String sql=" select * from BC_COLLECTION_INFO where SITE_ID='"+stationId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			result=row.get(0).getSafeString("DEVICE_NUM").toString();
		}
		return result;
	}
	/**
	 * 获取数采仪编码
	 * @param stationId 站点
	 * @return
	 */
	private String getMN(String stationId){
		String result="";
		String sql="select DEVICE_NUM from BC_COLLECTION_INFO where SITE_ID='"+stationId+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			result=row.get(0).getSafeString("DEVICE_NUM").toString();
		}
		return result;
		
	}
	/**
	 * 根据一个参数获取实际上传数据
	 * @param st 开始时间
	 * @param et 结束时间
	 * @return
	 */
	private String getTrueCount(String st,String et,String stationId,String flag){
		String result="";
		String sql="select count(*) as TOTAL from BD_MO_HOUR where PARMNAME='011' "+" and DATATIME >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and DATATIME <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and MN='"+stationId+"'";
		if (flag != null && !flag.equals("")) {
			sql += " and flag ='"+flag+"'";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		if(row.size()>0){
			result=row.get(0).getSafeString("TOTAL").toString();
		}
		return result;
	}
	/**
	 * 获取日常巡检子项有效性
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/getInspectionSubkey")
	public void getInspectionSubkey(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		  String st=RequestUnit.getParameter("START_TIME");
	      String et=RequestUnit.getParameter("END_TIME");
	      String stationId=RequestUnit.getParameter("STATION_ID");
	     // String wwtp=RequestUnit.getParameter("WWTP_ID");
	     // String flowname=RequestUnit.getParameter("FLOW_NAME");
	      String inspectionType=RequestUnit.getParameter("INSPECTION_TYPE");//类型  01 站房、辅助设备 02 采水、排水及内部管路 03 在线监测设备 04电路、仪器传输
	     /* List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
	      String sql="select * from BC_SITE_INFO where 1=1 ";
	      if(StringUtil.IsNotNullOrEmpty(wwtp)){
	    	    sql += " and  WWTP_ID=@wwtp";
				parms.add(new GlobalParameter("wwtp", wwtp));
	      }
        if(StringUtil.IsNotNullOrEmpty(flowname)){
      	  sql += " and  FLOW_TYPE=@flowname";
				parms.add(new GlobalParameter("flowname", flowname));
	      }
        List<RowBase> rows = DataUtil.getSelectRows(sql, parms); 
        if(rows.size()>0){
  			stationId=rows.get(0).getSafeString("EXP_NO");
  		 }*/
        RowBase data=new RowBase();
      //获取任务周期
        RowBase r=getJobCycle(stationId,"fa3ecf1a-4820-4f9f-b7b7-2553584ee7ba");
		//周期
		String cycle="";
		//事件名称
		String jobNmae="";
		//时间要求
		String cyclename="";
		//事件id
		String job_id="";
		if(r.size()>0){
			cycle=r.getSafeString("TASK_CYCLE");
			jobNmae=r.getSafeString("JOB_NAME");
			cyclename=r.getSafeString("CYCLENAME");
			job_id=r.getSafeString("JOB_ID");
			data.put("JOB_NAME", jobNmae);
			data.put("TIME_REQUIRE", cyclename);
		}
		else{
			data= null;
			
		}
		//获取应完成任务总数
	     String count=getTaskCount(cycle,st,et);
	     //实际完成任务数
	     int trueCount=getTaskTrueCount( "1", st, et, stationId);
	     String trueCountStr=trueCount+"";
	     if(trueCount>Integer.parseInt(count)){
	    	 trueCountStr=count;
	     }
	     float trueCountRate=trueCount/Float.parseFloat(count);
	     //获取按时完成率
	     String CompletionRate= getCompletionRate("1", st, et,stationId,trueCountRate);
	     //获取结果通过率
	    String PassingRate =getResultRate( "1", st, et,stationId);
	    String result= getInspectionSubkeyValid(st,et,stationId,inspectionType,PassingRate, CompletionRate);
	    data.put("TASK_TOTAL", count);
	    data.put("TASK_INTIMEPERCENT", CompletionRate);
	    data.put("PASSED_PERCENT", PassingRate);
	    data.put("VALIDATION_RESULT", result);
	    data.put("JOB_ID", job_id);
	    data.put("TASK_REAL", trueCountStr);
		List<RowBase> list=new ArrayList<RowBase>();
		list.add(data);
		JSONObject json = DataUtil.listToJSONObject(list, null, null);
		outString(json.toString());
	}
	/**
	 * 获取子项有效性
	 * @param st 开始时间
	 * @param et 结束时间
	 * @param stationId 站点id
	 * @param inspectionType 子项类型  1 站房、辅助设备 2 采水、排水及内部管路 3 在线监测设备 4电路、仪器传输
	 * @return
	 */
	private String getInspectionSubkeyValid(String st,String et,String stationId,String inspectionType,String PassingRate, String CompletionRate){
		String result="0";//0无效，1有效
		String sql="  select t3.exp_no,t3.name,t1.* from OP_RECO_RPT t1  "
				+ "left join OP_TASK t2 on t1.task_code=t2.task_code "
				+ "left join BC_SITE_INFO t3 on t2.station_id=t3.exp_no where 1=1 "
				+" and t1.create_on >=to_date('"+st+"','yyyy-MM-dd hh24:mi:ss')"+" and t1.create_on <=to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')"
				+" and t2.station_id='"+stationId+"'";
		if(inspectionType.equals("01")){
			sql+=" and (t1.item like '%A107_%' or t1.item like '%A110_%')";
		}
		if(inspectionType.equals("02")){
			sql+=" and  t1.item like '%A106_%' ";
		}
		if(inspectionType.equals("03")){
			sql+=" and (t1.item like '%A101_%' or t1.item like '%A102_%' or t1.item like '%A103_%' or t1.item like '%A104_%' or t1.item like '%A105_%')";
		}
		if(inspectionType.equals("04")){
			sql+=" and (t1.item like '%A108_%' or t1.item like '%A109_%')";
		}
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		float Nump = Float.parseFloat(PassingRate.replaceAll("%", ""));
		float Numc = Float.parseFloat(CompletionRate.replaceAll("%", ""));
		if(Nump>=75&&Numc>=75){
			result="1";
			if(row.size()>0){
				for(int i=0;i<row.size();i++){
					if(row.get(i).getSafeString("ITEM_VALUE").equals("0")){
						result="0";
						break;
					}
					else{
						result="1";
					}
				}
			}
			else{
				result="0";
			}
		}
		else{
			result="0";
		}
		
		return result;
	 }
	/**
	 * 国控污染源有效率算法
	 * @param resq
	 * @param res
	 * @throws Exception
	 */
	@RequestMapping(value="/getEfficient")
	public void getEfficient(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		DecimalFormat decimalFormat=new DecimalFormat("#.00##");
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map<String, String> wheres = getParams();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd ");  
		Date endDate = sdf1.parse(wheres.get("END_TIME"));  
	    Date startDate = sdf.parse(wheres.get("START_TIME")); 
	        //传输率的计算
	        /**
	         * 第一步计算应收条数
	         */
	        //1.1 获取时间跨度
			String hours=getStationTime(wheres.get("START_TIME"),wheres.get("END_TIME"));
			int hoursInt=Integer.parseInt(hours);
			//1.2 几小时上传一条数据
			String hour=wheres.get("HOUR");
			int hourInt=Integer.parseInt(hour);
			//1.3 获取站点参数数目
			List<RowBase> r=getpollParam(wheres.get("STATION_ID"));
			int m=r.size();
			//1.4 应收条数
			int RECEIVABLE_DATANUM=hoursInt*m/hourInt;
			if(RECEIVABLE_DATANUM<0) {
				RECEIVABLE_DATANUM = Math.abs(RECEIVABLE_DATANUM);
			}
			/**
			 * 第二步计算实收条数
			 */
			//2.1 获取mn号
			String mn=r.get(0).getSafeString("DEVICE_NUM");
			//2.2 计算实收每个参数之和
			double REAL_DATANUM=0;//实收条数
			String parmname="";
			for(int i=0;i<r.size();i++){
				RowBase re=r.get(i);
				parmname+="'"+re.getSafeString("NAME")+"',";
			}
			parmname=parmname.substring(0,parmname.length()-1);
			REAL_DATANUM=getpollData(mn,parmname,wheres.get("START_TIME"),wheres.get("END_TIME"));
			
			//判断是否多传，实收不能大于应收
			if(REAL_DATANUM>RECEIVABLE_DATANUM){
				REAL_DATANUM=RECEIVABLE_DATANUM;
			}
			/**
			 * 计算停电时间段实收条数以及应收条数
			 */
			List<RowBase> noPowerList = getPowerOutageListByStation(startDate,endDate,wheres.get("STATION_ID"));
			int NOPOWER_REAL_DATANUM=0;
			int NOPOWER_RECEIVABLE_DATANUM=0;
			int NOPOWER_REALVALIDATE_PACKETNUM=0;
			int NOPOWER_RECEIVABLEVALIDATE_PACKETNUM=0;
			for(RowBase row:noPowerList)
			{
				String nopower_start = row.getSafeString("NO_POWER_START_TIME");
				String nopower_end = row.getSafeString("NO_POWER_END_TIME");
				Date s = sdf1.parse(nopower_start);
				Date e = sdf1.parse(nopower_end);
				nopower_start = sdf1.format(s);
				nopower_end = sdf1.format(e);
				if(s.compareTo(startDate)>=0&&e.compareTo(endDate)<=0)
				{
					//叠加应收的数据条数
					NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))*m/hourInt;
					NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,nopower_end))/hourInt;
					//叠加实收的数据条数
					NOPOWER_REAL_DATANUM+=getpollData(mn,parmname,nopower_start,nopower_end);
					NOPOWER_REALVALIDATE_PACKETNUM+=getValidData(mn,nopower_start,nopower_end);
				}
				else if(s.compareTo(startDate)<=0&&e.compareTo(endDate)<=0)
				{
					//叠加应收的数据条数
					NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf1.format(startDate),nopower_end))*m/hourInt;
					NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf1.format(startDate),nopower_end))/hourInt;
					//叠加实收的数据条数
					NOPOWER_REAL_DATANUM+=getpollData(mn,parmname,sdf1.format(startDate),nopower_end);
					NOPOWER_REALVALIDATE_PACKETNUM+=getValidData(mn,sdf1.format(startDate),nopower_end);
				}
				else if(s.compareTo(startDate)>=0&&e.compareTo(endDate)>=0)
				{
					//叠加应收的数据条数
					NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(nopower_start,sdf1.format(endDate)))*m/hourInt;
					NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(nopower_start,sdf1.format(endDate)))/hourInt;
					//叠加实收的数据条数
					NOPOWER_REAL_DATANUM+=getpollData(mn,parmname,nopower_start,sdf1.format(endDate));
					NOPOWER_REALVALIDATE_PACKETNUM+=getValidData(mn,nopower_start,sdf1.format(endDate));
					
				}
				else
				{
					//叠加应收的数据条数
					NOPOWER_RECEIVABLE_DATANUM+=Integer.parseInt(getStationTime(sdf1.format(startDate),sdf1.format(endDate)))*m/hourInt;
					NOPOWER_RECEIVABLEVALIDATE_PACKETNUM+=Integer.parseInt(getStationTime(sdf1.format(startDate),sdf1.format(endDate)))/hourInt;
					//叠加实收的数据条数
					NOPOWER_REAL_DATANUM+=getpollData(mn,parmname,sdf1.format(startDate),sdf1.format(endDate));
					NOPOWER_REALVALIDATE_PACKETNUM+=getValidData(mn,sdf1.format(startDate),sdf1.format(endDate));
				}
			}
			REAL_DATANUM=REAL_DATANUM-NOPOWER_REAL_DATANUM;
			RECEIVABLE_DATANUM=RECEIVABLE_DATANUM-NOPOWER_RECEIVABLE_DATANUM;
			/**
			 * 第三步 计算传输率
			 */
			double transRate=0;
			if(RECEIVABLE_DATANUM>0) {
				transRate = (double)REAL_DATANUM/(double)RECEIVABLE_DATANUM;
			}
			String TRANSFER_RATE=decimalFormat.format(transRate);
			transRate = Double.valueOf(TRANSFER_RATE);
		//有效率的计算
			/**
			 *第一步计算应收组数 
			 */
			int RECEIVABLEVALIDATE_PACKETNUM=hoursInt/hourInt;
			/**
			 * 第二步计算有效组数
			 */
			int REALVALIDATE_PACKETNUM=getValidData(mn,wheres.get("START_TIME"),wheres.get("END_TIME"));
			//计算流量为零的数据组数
			int invalidate_packetnum = getB01ZeroData(mn,wheres.get("START_TIME"),wheres.get("END_TIME"));
			//统计数据修约后 没有上传凭证的数据组数
			int unLoadCertificatePacketNum = getUnLoadCertificatePacketNum(mn,wheres.get("START_TIME"),wheres.get("END_TIME"));
			//获取停产停电故障时间产生的数据组数（这种情况要从实际有效组数中减去）
			int ERROR_PACKETNUM=getHoursDistance(wheres.get("ERROR_START_TIME"),wheres.get("ERROR_END_TIME"));
			
			//有效组数=实际应该有效数组个数-流量为0数组个数-修约后没有上传凭证的数据组数-停产停电时间段的数据组数
			REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-invalidate_packetnum-ERROR_PACKETNUM-unLoadCertificatePacketNum;
			REALVALIDATE_PACKETNUM = REALVALIDATE_PACKETNUM-NOPOWER_REALVALIDATE_PACKETNUM;
			//可能会产生负数，负数的一律为0
			if(REALVALIDATE_PACKETNUM<0) {
				REALVALIDATE_PACKETNUM=0;
			}
			//停产停电的情况也要从应收数据组数中减去停电未传数据组数。
			RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-ERROR_PACKETNUM-invalidate_packetnum;
			RECEIVABLEVALIDATE_PACKETNUM = RECEIVABLEVALIDATE_PACKETNUM-NOPOWER_RECEIVABLEVALIDATE_PACKETNUM;
			if(RECEIVABLEVALIDATE_PACKETNUM<0) {
				RECEIVABLEVALIDATE_PACKETNUM=0;
			}
			//有效组数不能大于应收组数
			if(REALVALIDATE_PACKETNUM>RECEIVABLEVALIDATE_PACKETNUM){
				REALVALIDATE_PACKETNUM=RECEIVABLEVALIDATE_PACKETNUM;
			}

			/**
			 * 第三步计算有效率
			 */
			double rateAll=0;
			double rateVali=0;
			double validataRate=0;
			//为了避免分母为零引起的报错
			if(RECEIVABLEVALIDATE_PACKETNUM>0) {
				rateVali=(float)REALVALIDATE_PACKETNUM/(float)RECEIVABLEVALIDATE_PACKETNUM;
				String VALIDATE_RATE=decimalFormat.format(rateVali);
				validataRate = Double.valueOf(VALIDATE_RATE);
			}
			//传输有效率计算
			rateAll=transRate*rateVali;
			String Allrate=decimalFormat.format(rateAll);
			double transValidateRate = Double.valueOf(Allrate);
			List<RowBase> result=new ArrayList<RowBase>();
			RowBase rb=new RowBase();
			rb.put("REAL_DATANUM", REAL_DATANUM);
			rb.put("RECEIVABLE_DATANUM", RECEIVABLE_DATANUM);
			rb.put("TRANSFER_RATE", transRate);
			rb.put("RECEIVABLEVALIDATE_PACKETNUM", RECEIVABLEVALIDATE_PACKETNUM);
			rb.put("REALVALIDATE_PACKETNUM", REALVALIDATE_PACKETNUM);
			rb.put("VALIDATE_RATE", validataRate);
			rb.put("TRANSFER_VALIDATE_RATE", transValidateRate);
			result.add(rb);
			//删除并插入新计算的传输有效率
			deleteAndInsertTransValidationRate(rb,wheres,endDate);
			//将手动计算的结果返回前端
			JSONObject json = DataUtil.listToJSONObject(result, null, null);
			outString(json.toString());
	}
	@RequestMapping("/queryEfficient")
	public void queryEfficient(HttpServletRequest resq, HttpServletResponse res) throws Exception {
		Map<String, String> wheres = getParams();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	    Date endDate = sdf.parse(wheres.get("END_TIME"));  
	    String stationID = wheres.get("STATION_ID");
	    List<String> dataText = getM2MDateText(endDate,1,wheres.get("DATA_TYPE"));
	    String querySql="select *  from data_trans_validation t where t.station_id=@station_id and t.data_text=@data_text";
		List<GlobalParameter> parms = new ArrayList<>();
		if(dataText.size()>0) {
			parms.add(new GlobalParameter("station_id", stationID));
			parms.add(new GlobalParameter("data_text", dataText.get(0)));
		    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
		    JSONObject json = DataUtil.listToJSONObject(result, null, null);
		    outString(json.toString());
		}
	}
	/**
	 * 获取站点参数
	 * @param stationId
	 * @return
	 */
	private List<RowBase> getpollParam(String stationId){
		String sql="select t.name,t1.device_num,t.site_id from BC_SITE_POLL t left join BC_COLLECTION_INFO t1 on t.site_id=t1.site_id where t.site_id='"+stationId+"'  group by t.name,t1.device_num,t.site_id";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row;
	}
	/**
	 * 获取时间段内参数条数
	 * @param mn
	 * @param parmname
	 * @param st
	 * @param et
	 * @return
	 */
	private int getpollData(String mn,String parmname,String st ,String et){
		String sql="select t.*  from BD_MO_HOUR t where t.mn='"+mn+"' and t.parmname in ("+parmname+") and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss')";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		return row.size();
	}
	/**
	 * 获取有效组数()
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	private int getValidData(String mn,String st ,String et){
		String sql="select count(*) as sum  from (select distinct (t.mn||to_char(t.datatime,'yyyy-MM-dd HH24:mi:ss')) as guid from BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss'))";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.get(0).getSafeInt("SUM");
		}
		return result;
	}
	
	/**
	 * 获取流量为0的数据条数
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	private int getB01ZeroData(String mn,String st ,String et){
		String pollSql = "select t2.name\n" +
						"  from bc_collection_info t\n" + 
						"  left join bc_site_poll t2 on t.site_id = t2.site_id\n" + 
						" where t.device_num = '"+mn+"'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> pollrow = DataUtil.getSelectRows(pollSql, parms);
		//调整流量筛选策略，如果该站点有瞬时流量，则根据瞬时流量来统计流量为0的条数，否则根据累计流量统计
		String paramName="B01TOTAL";
		if(pollrow!=null && pollrow.size()>0) {
			for(int i = 0;i < pollrow.size();i++) {
				RowBase row = new RowBase();
				row = pollrow.get(i);
				String pollName = row.getSafeString("NAME");
				if("B01".equals(pollName)){
					paramName = "B01";
					break;
				}
			}
		}
		String sql="select t.parmname,t.datatime,t.avg  from  bd_mo_hour t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"
				   + " and t.avg=0 and t.parmname='"+paramName+"'\n"
				   + " group by t.parmname,t.datatime,t.avg";
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	/***
	 * 根据故障时间计算缺失数据组数
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 * @throws ParseException 
	 */
	private int getHoursDistance(String errorStartTime,String errorEndTime) throws ParseException{
		int hours=0;
		int totalHours=0;
		if(StringUtils.IsNullOrEmpty(errorStartTime) ||StringUtils.IsNullOrEmpty(errorEndTime)) {
			return hours;
		}
		String[] startTime = errorStartTime.split(",");
		String[] endTime = errorEndTime.split(",");
		for(int i = 0;i<startTime.length;i++) {
			String starts = startTime[i];
			String ends = endTime[i];
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			Date start = dateFormat.parse(starts);
			Date end =dateFormat.parse(ends);
			if(start.after(end)) {
				return hours;
			}
			long StartMills = start.getTime();
			long EndMills = end.getTime();
			long startHours = StartMills/(3600*1000);
			long endHours = EndMills/(3600*1000);
			long millsDistance = EndMills-StartMills;
			if(millsDistance>0 && millsDistance<=3600000) {
				hours=1;
			} else {
				hours = (int)Math.ceil(endHours-startHours);
			}
			totalHours+=hours;
		}
		return totalHours;
	}
	
	/**
	 * 获取修约后未上传凭证的数据组数
	 * @param mn
	 * @param st
	 * @param et
	 * @return
	 */
	private int getUnLoadCertificatePacketNum(String mn,String st ,String et){
		String sql="select t.datatime  from  BD_MO_HOUR t where t.flag ='N' and t.mn='"+mn+"' and t.datatime >= to_date('\n"
				   +st+"','yyyy-MM-dd hh24:mi:ss') and t.datatime <= to_date('"+et+"','yyyy-MM-dd hh24:mi:ss') \n"+
				   " and t.data_from>0 and t.modify_certificate_id is null \n"+
				   " and t.modify_date is not null \n"+
				   " group by t.datatime ";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> row = DataUtil.getSelectRows(sql, parms);
		int result=0;
		if(row.size()>0){
			result=row.size();
		}
		return result;
	}
	
	/**
	 * 获取环比时间点集
	 * gislin
	 * 2016年8月27日
	 * @param endDate
	 * @param dateLength
	 * @param cycleTypeStr day,week,month,quarter,year
	 * @return
	 * @throws ParseException
	 */
	private List<String> getM2MDateText(Date endDate,int dateLength,String cycleTypeStr) throws ParseException{
		
		List<String> dateTexts = new ArrayList<>();
		List<String> resultDateTexts = new ArrayList<>();
		
		Calendar currentCal = Calendar.getInstance();
		currentCal.setTime(endDate);
		
		//如果是季度的话，把月置为每个季度的第一个月
		if("quarter".equals(cycleTypeStr)){
			int month = currentCal.get(Calendar.MONTH);
			if(month <= 3){
				currentCal.set(Calendar.MONTH, 1);
			}
			else if(month <= 6){
				currentCal.set(Calendar.MONTH, 4);
			}
			else if(month <= 9){
				currentCal.set(Calendar.MONTH, 7);
			}
			else if(month <= 12){
				currentCal.set(Calendar.MONTH, 10);
			}
		}
		
		for(int i=0;i<dateLength;i++){
			
			int year = currentCal.get(Calendar.YEAR);
			int month = currentCal.get(Calendar.MONTH);
			int week = currentCal.get(Calendar.WEEK_OF_YEAR);
			
			String curMonth = "";
			if(month+1 < 10){
				curMonth = "0"+(month+1);
			}
			else{
				curMonth = month+1+"";
			}
			if("week".equals(cycleTypeStr)){
				dateTexts.add(year + "年" + week+"周");
				currentCal.add(Calendar.WEEK_OF_YEAR, -1);
			}
			else if("month".equals(cycleTypeStr)){
				dateTexts.add(year + "年" + curMonth+"月");
				currentCal.add(Calendar.MONTH, -1);
			}
		}
		for(int i=dateTexts.size()-1;i>=0;i--){
			resultDateTexts.add(dateTexts.get(i));
		}
		
		return resultDateTexts;
	}
	/**
	 * 删除旧的传输有效率，插入新的传输有效率。
	 * @param param
	 * @param wheres
	 * @param endDate
	 */
	
	public void deleteAndInsertTransValidationRate(RowBase param,Map<String, String> wheres,Date endDate) {
		try {
			List<String> dataText = getM2MDateText(endDate,1,wheres.get("DATA_TYPE"));
			List<GlobalParameter> parms = new ArrayList<>();
			String stationId = wheres.get("STATION_ID");
			String dataTexts = dataText.get(0);		
			double realNum = param.getSafeDouble("REAL_DATANUM");
			double receiveNum = param.getSafeDouble("RECEIVABLE_DATANUM");
			double transRate = param.getSafeDouble("TRANSFER_RATE");
			double receiverablePacketNum = param.getSafeDouble("RECEIVABLEVALIDATE_PACKETNUM");
			double v_realvalidate_packetnum = param.getSafeDouble("REALVALIDATE_PACKETNUM");
			double v_validate_rate = param.getSafeDouble("VALIDATE_RATE");
			double v_transfer_validate_rate = param.getSafeDouble("TRANSFER_VALIDATE_RATE");
			parms.add(new GlobalParameter("STATION_ID", stationId));
			parms.add(new GlobalParameter("v_data_text", dataTexts));
			
			String deleteSql = "delete from data_trans_validation t where t.station_id=@STATION_ID and t.data_text=@v_data_text";
			String insertSql ="insert into data_trans_validation\n" +
				"  (guid, station_id, data_text, real_datanum, receivable_datanum, transfer_rate, receivablevalidate_packetnum, realvalidate_packetnum, validate_rate, transfer_validate_rate)\n" + 
				"values\n" + 
				"  (sys_guid(),"+ "'"+stationId+"',"+"'"+dataTexts+"',"+""+realNum+","+""+receiveNum+"," +""+transRate+","+""+receiverablePacketNum+"," 
				+""+v_realvalidate_packetnum+"," +""+v_validate_rate+","+ ""+v_transfer_validate_rate+")";
			DataUtil.ExecuteUpdateSql(deleteSql, parms);
			DataUtil.ExecuteUpdateSql(insertSql, null);
		} catch (ParseException e) {
			e.printStackTrace();
		}	
	}
	@RequestMapping("/queryNoPowerByStationId")
	public void queryNoPowerByStationId(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		Map<String, String> params = getParams();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	    Date endDate = sdf.parse(params.get("END_TIME"));  
	    Date startDate = sdf.parse(params.get("START_TIME")); 
	    String stationID = params.get("STATION_ID");
	    String querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n"
	    		+ " and t.no_power_start_time>=@startDate\n"
	    		+" and  t.no_power_start_time <=@endDate\n"
	    		+" and  t.no_power_end_time >=@startDate\n"
	    		+" and  t.no_power_end_time <=@endDate\n";
	    querySql+=" order by t.no_power_start_time";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("station_id", stationID));
		parms.add(new GlobalParameter("startDate", startDate));
		parms.add(new GlobalParameter("endDate", endDate));
	    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
	    JSONObject json = DataUtil.listToJSONObject(result, null, null);
	    outString(json.toString());
	}
	@RequestMapping("/queryPowerOutage")
	public void queryPowerOutage(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		Map<String, String> params = getParams();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
	    Date endDate = sdf.parse(params.get("END_TIME"));  
	    Date startDate = sdf.parse(params.get("START_TIME")); 
	    String stationID = RequestUnit.getParameter("MN");
	    String querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n"
	    		+ " and t.no_power_start_time>=@startDate\n"
	    		+" and  t.no_power_start_time <=@endDate\n"
	    		+" and  t.no_power_end_time >=@startDate\n"
	    		+" and  t.no_power_end_time <=@endDate\n";
	    querySql+=" order by t.no_power_start_time";
	    if(stationID.equals("0"))
	    {
	    	 querySql="select *  from BC_SNP_INFO t where  t.no_power_start_time>=@startDate\n"
	 	    		+" and  t.no_power_start_time <=@endDate\n"
	 	    		+" and  t.no_power_end_time >=@startDate\n"
	 	    		+" and  t.no_power_end_time <=@endDate\n";
	 	    querySql+=" order by t.no_power_start_time";
	    }
	   
		List<GlobalParameter> parms = new ArrayList<>();
		if(!stationID.equals("0"))
	    {
			parms.add(new GlobalParameter("station_id", stationID));
	    }
		parms.add(new GlobalParameter("startDate", startDate));
		parms.add(new GlobalParameter("endDate", endDate));
	    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
	    JSONObject json = DataUtil.listToJSONObject(result, null, null);
	    outString(json.toString());
	}
	
	@RequestMapping("/queryNoPowerOnlyByStationId")
	public void queryNoPowerOnlyByStationId(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		Map<String, String> params = getParams();  
	    String stationID = params.get("STATION_ID");
	    String flag = params.get("FLAG");
	    String querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n";
	    querySql+=" order by t.no_power_start_time";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("station_id", stationID));
		if(flag!=null&&flag.equals("edit"))
	    {
		    String GUID = params.get("GUID");
			querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n"
		    		+ " and t.GUID not in ('"+GUID+"')\n";
		    querySql+=" order by t.no_power_start_time";
		    
	    }
	    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
	    JSONObject json = DataUtil.listToJSONObject(result, null, null);
	    outString(json.toString());
	}
	@RequestMapping("/updateNoPowerTime")
	public void updateNoPowerTime(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		String NO_POWER_END_TIME=RequestUnit.getParameter("NO_POWER_END_TIME");
		String NO_POWER_START_TIME=RequestUnit.getParameter("NO_POWER_START_TIME");
		String GUID=RequestUnit.getParameter("GUID");
		String updateTime=RequestUnit.getParameter("UPDATE_TIME");
		Date upDate = sdf.parse(updateTime);
		Date startDate = null;
		Date endDate = null;
		if(!"".equals(NO_POWER_START_TIME)) {
			startDate = sdf.parse(NO_POWER_START_TIME);
		}
		if(!"".equals(NO_POWER_END_TIME)) {
			endDate = sdf.parse(NO_POWER_END_TIME);
		}
	    String querySql="UPDATE  BC_SNP_INFO t SET T.NO_POWER_START_TIME =@NO_POWER_START_TIME,\n"
	    		+ " T.NO_POWER_END_TIME =@NO_POWER_END_TIME,\n"
	    		+"  t.UPDATE_TIME = @UPDATE_TIME\n"
	    		+ "  where t.GUID=@GUID \n";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("NO_POWER_START_TIME", startDate));
		parms.add(new GlobalParameter("NO_POWER_END_TIME", endDate));
		parms.add(new GlobalParameter("GUID", GUID));
		parms.add(new GlobalParameter("UPDATE_TIME", upDate));
	    DataUtil.ExecuteUpdateSql(querySql, parms);
	}
	@RequestMapping("/updatePowerOutage")
	public void updatePowerOutage(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		String NO_POWER_END_TIME=RequestUnit.getParameter("NO_POWER_END_TIME");
		String NO_POWER_START_TIME=RequestUnit.getParameter("NO_POWER_START_TIME");
		String MN=RequestUnit.getParameter("MN");
		String GUID=RequestUnit.getParameter("GUID");
		String MARK=RequestUnit.getParameter("MARK");
		Date startDate = null;
		Date endDate = null;
		if(!"".equals(NO_POWER_START_TIME)) {
			startDate = sdf.parse(NO_POWER_START_TIME);
		}
		if(!"".equals(NO_POWER_END_TIME)) {
			endDate = sdf.parse(NO_POWER_END_TIME);
		}
	    String querySql="UPDATE  BC_SNP_INFO t SET T.NO_POWER_START_TIME =@NO_POWER_START_TIME,\n"
	    		+ " T.NO_POWER_END_TIME =@NO_POWER_END_TIME,\n"
	    		+"  t.MARK = @MARK\n"
	    		+ "  where t.GUID=@GUID \n";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("NO_POWER_START_TIME", startDate));
		parms.add(new GlobalParameter("NO_POWER_END_TIME", endDate));
		parms.add(new GlobalParameter("GUID", GUID));
		parms.add(new GlobalParameter("MARK", MARK));
	    DataUtil.ExecuteUpdateSql(querySql, parms);
	}
	@RequestMapping("/deleteNoPowerTime")
	public void deleteNoPowerTime(HttpServletRequest resq, HttpServletResponse res)throws Exception {
		String GUID=RequestUnit.getParameter("GUID");
	    String updateSql="DELETE FROM   BC_SNP_INFO t  where t.GUID=@GUID \n";
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("GUID", GUID));
	    DataUtil.ExecuteUpdateSql(updateSql, parms);
	}
	public List<RowBase> getPowerOutageListByStation(Date start,Date end,String stationID)
	{
	    String querySql="select *  from BC_SNP_INFO t where t.station_id=@station_id \n"
	    		+" and  t.no_power_start_time <=@endDate\n"
	    		+" and  t.no_power_end_time >=@startDate\n";
	    querySql+=" order by t.no_power_start_time";
	    
		List<GlobalParameter> parms = new ArrayList<>();
		parms.add(new GlobalParameter("station_id", stationID));
		parms.add(new GlobalParameter("startDate", start));
		parms.add(new GlobalParameter("endDate", end));
	    List<RowBase> result = DataUtil.getSelectRows(querySql, parms);
	    return result;
	}
}
